package data;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashSet;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

public class UserCase {
	private Connection conn;

	public UserCase() throws Exception {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		String url = "jdbc:mysql://localhost:3306/" + "bibsonomy";
		String dbuser = "root";
		String dbpass = "123456";
		Connection con = (Connection) DriverManager.getConnection(url, dbuser,
				dbpass);
		this.conn = con;
	}

	private HashSet<String> getAllUsers() throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		HashSet<String> users = new HashSet<String>();
		try {
			stmt = (Statement) conn.createStatement();
			rs = (ResultSet) stmt
					.executeQuery("SELECT DISTINCT(USER) FROM TAS_NEW");
			while (rs.next()) {
				String user = rs.getString("user");
				users.add(user);
			}

		} finally {

			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				} // ignore
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
				} // ignore
				stmt = null;
			}
		}
		return users;
	}

	public void userTagItems() throws Exception {
		HashSet<String> users = getAllUsers();
		Statement stmt = null;
		ResultSet rs = null;
		String path = "D:\\CityU\\project\\bibsonomy\\data\\user_itemNum.txt";
        BufferedWriter writer = new BufferedWriter(new FileWriter(path));
		try {
			stmt = (Statement) conn.createStatement();

			for (String user : users) {
				rs = (ResultSet) stmt
						.executeQuery("select count(distinct(t.content_id)) from tas_new t, bibtex b where t.user = '"
								+ user + "' and b.content_id = t.content_id;");
				rs.next();
				int cnt = rs.getInt("count(distinct(t.content_id))");
				System.out.println(user + "\t" + cnt);
				writer.write(user + "\t" + cnt);
                writer.newLine();
			}

		} finally {
			writer.flush();
			writer.close();
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				} // ignore
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
				} // ignore
				stmt = null;
			}

		}

	}
	
	
	public static void main(String[] args) throws Exception{
		UserCase app = new UserCase();
		app.userTagItems();
		
		
	}
	

}
